#!/usr/bin/python
# -*- coding : UTF-8 -*-
# ========================
# @Time  : 2020/5/8 11:42
# @Author: Mecell
# @File  : excel_packaging.py
# ========================

import os
import xlrd
from collections import namedtuple

from openpyxl import load_workbook

from common.log_packaging import logger



class ExcelHandle:
    """
    定义一个处理excel文件的类
    """
    def __init__(self, filename, sheetname=None):
        self.filename = filename
        self.sheetname = sheetname
        self.wb = load_workbook(filename=self.filename)
        self.ws = self.wb[self.sheetname] if self.sheetname is not None else self.wb.active
        self.fields_tuple = tuple(self.ws.iter_rows(max_row=1, values_only=True))[0]
        self.fields_namedtuple = namedtuple("fields_namedtuple", self.fields_tuple)
        self.cases_list = []

    def get_all_cases(self, min_row=2):
        """
        获取所有数据
        :param min_row: 默认从第二行开始读取excel数据
        :return: 返回嵌套命名元组的列表
        """
        for case_tuple in self.ws.iter_rows(min_row, values_only=True):
            self.cases_list.append(self.fields_namedtuple(*case_tuple))
        return self.cases_list

    def get_case_tuple(self, row):
        """
        获取某行的数据，只能通过索引取值
        :param row: 行号，读取哪行的数据
        :return: 返回单元格数据组成的元组
        """
        try:
            if isinstance(row, int) and (2 <= row <= self.ws.max_row):
                return tuple(self.ws.iter_rows(min_row=row, max_row=row, values_only=True))[0]
        except ValueError:
            logger.exception("行号有误，行号应为大于1的整数！")
        except TypeError:
            logger.exception("行号有误，行号应为大于1的整数！")

    def get_case_namedtuple(self, row, min_row=2):
        """
        获取某行的数据，可以通过字段名(属性)取值
        :param row: 行号，读取哪行的数据
        :return: 返回单元格数据组成的命名元组
        """
        for case_tuple in self.ws.iter_rows(min_row, values_only=True):
            self.cases_list.append(self.fields_namedtuple(*case_tuple))
        return self.cases_list[row-2]

    def get_data_from_col_num(self, column):
        """
        通过传入列号获取某列的数据
        :param column:
        :return:
        """
        try:
            if isinstance(column, int) and (0 < column <= self.ws.max_column) :
                return tuple(self.ws.iter_rows(min_col=column, max_col=column, min_row=2, values_only=True))
        except ValueError:
            logger.exception("列号有误，行号应为大于0的整数！")
        except TypeError:
            logger.exception("列号有误，行号应为大于0的整数！")

    def get_data_from_col_name(self, field):
        """
        通过传入字段名获取字段的数据组成的列表
        :param column:
        :return:
        """
        value_list = []
        for i in self.get_all_cases():
            value = getattr(i, field)
            value_list.append(value)
        return value_list

    def sum_col_value(self, field):
        """
        统计某列的值的总和
        :return:
        """
        return sum(self.get_data_from_col_name(field))

    def write_result(self, *args, **kwargs):
        """
        写入结果到excel文件中
        :param args: 行号，可多个
        :param kwargs: 列号及数值组成的字典
        :return:
        """
        w_wb = load_workbook(self.filename)
        w_ws = w_wb[self.sheetname]
        for row in args:
            if isinstance(row, int) and (row >= 2):
                    for column, value in kwargs.items():
                            w_ws.cell(row=row, column=int(column), value=value)
            else:
                logger.exception("行号或列号有误，行号应为大于初始行的整数，列号应为整数！")
        w_wb.save(self.filename)
        w_wb.close()



class ReadXlsExcel:
    """
    定义一个处理xls格式的excel文件的类
    """

    def __init__(self, filename, sheetname=None, sheetx=0, sheet_flag="index"):
        self.filename = filename
        self.sheetname = sheetname
        self.sheetx = sheetx
        self.sheet_flag = sheet_flag
        self.wb = xlrd.open_workbook(filename=self.filename)
        self.ws = self.wb.sheet_by_index(sheetx) if self.sheet_flag == "index" else self.wb.sheet_by_name(sheetname)

    def get_rows_amount(self):
        """
        获取行记录总数(排除第一行抬头行)
        :return:
        """
        return self.ws.nrows - 1

    def get_row_value(self, row):
        """
        获取某一行的内容
        :return:
        """
        return self.ws.row_values(row-1)

    def get_col_value(self, col):
        """
        获取某一列的内容
        :return:
        """
        return self.ws.col_values(col-1)

    def get_cell_value(self, row, col):
        """
        获取某个单元格的内容
        :param col:
        :param row:
        :return:
        """
        return self.ws.cell_value(row-1, col-1)

if __name__ == '__main__':
    from common.path_constants import ASSET_IMP,EXPORT_DIR,TRAINING_IMP
    # excel_obj = ReadXlsExcel(os.path.join(TRAINING_IMP,'list_export.xls'))
    excel_obj = ReadXlsExcel(os.path.join(TRAINING_IMP,'imporCaseCenter.xlsx'))
    excel_obj2 = ExcelHandle(os.path.join(ASSET_IMP,'inventoryImport.xlsx'))
    print(excel_obj2.sum_col_value("数量"))
    print(excel_obj.get_rows_amount(),type(excel_obj.get_rows_amount()))
    # print(excel_obj.get_row_value(1))
    # print(excel_obj.get_col_value(5))
    # print(excel_obj.get_cell_value(2,1))

    # 写入数据
    # row = (12,13,16,19)
    # col_value = {"10": "太阳", "11": "月亮"}
    # print(col_value[10])
    # print(col_value["11"])
    # for x,y in col_value.items():
    #     print(type(x),type(y))
    # excel_obj.write_result(*row,**col_value)
    pass
